package com.uplift.common.utils;

import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.read.biff.BiffException;
import org.apache.commons.lang3.StringUtils;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public final  class JExcelReadUtils {

	
	/**
	 * 获取列头
	 * **/
	public static String[] readColHeader(final String excelFilePath) throws BiffException, IOException {
		File excelFile = new File(excelFilePath);
		Workbook workbook = Workbook.getWorkbook(excelFile);
		if (workbook.getSheets().length <= 0) {
			return null;
		}
		Sheet sheet = workbook.getSheet(0);
		int clounms = sheet.getColumns();
		String[] ra=new String[clounms];
		for (int index = 0; index < clounms; index++) {
			Cell cell = sheet.getCell(index, 0);
			ra[index]=cell.getContents();
		}
		workbook.close();
		return ra;
	}
	
	/**
	 * 读取Excel文件中Sheet1的内容，以键值对的形式存在List中,包含Excel中的首列数据
	 * @param excelFilePath
	 * @param endColunmCounts
	 * @param startRows
	 * @param endRows
	 * @return
	 * @throws BiffException
	 * @throws IOException
	 */
	public static List<Map<String, String>> readExcelSheet1(
			String excelFilePath, int endColunmCounts, int startRows,
			int endRows) throws BiffException, IOException {
		File excelFile = new File(excelFilePath);
		FileInputStream is = new FileInputStream(excelFile);
		return readExcelSheet1(is, endColunmCounts, startRows, endRows);
	}

	/**
	 * 读取Excel文件中Sheet1的内容，以键值对的形式存在List中,包含Excel中的首列数据
	 * @param is
	 * @param endColunmCounts
	 * @param startRows
	 * @param endRows
	 * @return
	 * @throws BiffException
	 * @throws IOException
	 */
	public static List<Map<String, String>> readExcelSheet1(
			InputStream is, int endColunmCounts, int startRows,
			int endRows) throws BiffException, IOException {
		// 数据起始行不能大于终止行
		if (endRows!=-1 && startRows>endRows) {
			return null;
		}
		// 读取数据的行数和列数
		int columns = 0;
		int rows = 0;
		Workbook workbook = Workbook.getWorkbook(is);
		if (workbook.getSheets().length <= 0) {
			return null;
		}
		Sheet sheet = workbook.getSheet(0);
		// 设置结束列
		if (endColunmCounts > 0) {
			columns = endColunmCounts;
		} else {
			columns = sheet.getColumns();
		}
		// 设置结束行
		if (endRows > 0) {
			rows = endRows;
		} else {
			rows = sheet.getRows();
		}
		// 用来存储sheet1中的数据
		List<Map<String, String>> datas = new ArrayList<Map<String, String>>();
		for (int i = startRows > 0 ? startRows - 1 : 0; i < rows; i++) {
			// 用来存储row中的数据
			Map<String, String> rowData = new HashMap<String, String>();

			boolean isAddDatas = false;
			for (int j = 0; j < columns; j++) {
				Cell cell = sheet.getCell(j, i);

				if(StringUtils.isNotBlank(cell.getContents())) {
					isAddDatas = true;
				}
				rowData.put(String.valueOf(j), cell.getContents());
			}

			if(isAddDatas) {
				datas.add(rowData);
			}
		}
		workbook.close();
		return datas;
	}


	/**
	 * 判断一个String[]数组中是否包含某个指定的字符串
	 * 
	 * @param strGather
	 * @param strUnit
	 * @return
	 */
	private static boolean ContainString(String[] strGather, String strUnit) {
		for (String str : strGather) {
			if (str.equals(strUnit)) {
				return true;
			}
		}
		return false;
	}

	public static List<Map<String, String>> readExcelSheet1(
			String excelFilePath, String[] colunmsIndex, int startRows,
			int endRows) throws BiffException, IOException {
		// 数据起始行不能大于终止行
		if (startRows > endRows) {
			return null;
		}
		int rows = 0;
		int clounms = 0;
		File excelFile = new File(excelFilePath);
		Workbook workbook = Workbook.getWorkbook(excelFile);
		if (workbook.getSheets().length <= 0) {
			return null;
		}
		Sheet sheet = workbook.getSheet(0);

		if (endRows > 0) {
			rows = endRows;
		} else {
			rows = sheet.getRows();
		}
		clounms = sheet.getColumns();

		// 用来存储列头数据
		Map<String, String> firstRowData = new HashMap<String, String>();
		for (int index = 0; index < clounms; index++) {
			Cell cell = sheet.getCell(index, 0);
			firstRowData.put(String.valueOf(index), cell.getContents());
		}

		// 用来存储sheet1中的数据
		List<Map<String, String>> datas = new ArrayList<Map<String, String>>();
		for (int i = startRows > 1 ? startRows - 1 : 1; i < rows; i++) {
			// 用来存储row中的数据
			Map<String, String> rowData = new HashMap<String, String>();
			for (int j = 0; j < clounms; j++) {
				if (ContainString(colunmsIndex,
						firstRowData.get(String.valueOf(j)))) {
					Cell cell = sheet.getCell(j, i);
					rowData.put(firstRowData.get(String.valueOf(j)), cell.getContents());
				}
			}
			datas.add(rowData);
		}
		workbook.close();
		return datas;
	}

	/**
	 * 读取Excel文件中所有Sheet的内容，以键值对的形式存在List中
	 * @param filePhth
	 * @return
	 * @throws BiffException
	 * @throws IOException
	 */
	public static List<ArrayList<Map<String, String>>> readExcelAllSheets(
			String filePhth) throws BiffException, IOException {
		File excelFile = new File(filePhth);
		Workbook workbook = Workbook.getWorkbook(excelFile);
		Sheet[] sheets = workbook.getSheets();
		if (sheets.length <= 0) {
			return null;
		}
		// 用来存储所有sheets中的数据
		List<ArrayList<Map<String, String>>> datas = new ArrayList<ArrayList<Map<String, String>>>();
		for (int i = 0; i < sheets.length; i++) {
			Sheet sheeti = sheets[i];
			int colunmsi = sheeti.getColumns();
			int rowsi = sheeti.getRows();
			// 用来存储sheeti中的数据
			ArrayList<Map<String, String>> datasi = new ArrayList<Map<String, String>>();
			for (int j = 0; j < rowsi; j++) {
				// 用来存储jrow中的数据
				Map<String, String> rowdata = new HashMap<String, String>();
				for (int k = 0; k < colunmsi; k++) {
					Cell cell = sheeti.getCell(k, j);
					rowdata.put(String.valueOf(k), cell.getContents());
				}
				datasi.add(rowdata);
			}
			datas.add(datasi);
		}
		workbook.close();
		return datas;
	}

	/**
	 * 将传进来的列头和记录进行组装
	 * @param heard
	 * @param record
	 * @return
	 */
	public static Map<String,Object> packageRecord(Map<String, String> heard,Map<String,String> record){
		Map<String,Object> map=new HashMap<String, Object>();
		for(int i=0;i<heard.size();i++){
			map.put(heard.get(String.valueOf(i)), record.get(String.valueOf(i)));
		}
		return map;
	}

	/**
	 * 检查excle格式
	 * @param data
	 * @return
	 */
	public static boolean checkImprtExcelFormat(Map<String, String> data, String[] excel_cloum_names) {
		if(data == null) {
			return false;
		}
		for (int i = 0; i < data.keySet().size(); i++) {

			if(i>=excel_cloum_names.length) {
				return true;
			}

			if(!StringUtils.equals(data.get(""+i), excel_cloum_names[i])) {
				return false;
			}
		}

		return true;
	}
}
